package com.elitel.frame.web.cfgdbinfo.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.elitel.common.mongodb.MongoDBUtil;
import com.elitel.common.utils.SysUserUtils;
import com.elitel.frame.base.service.CfgTableService;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.web.cfgdbinfo.dao.CfgDbInfoDao;
import com.elitel.frame.web.cfgdbinfo.entity.CfgDbInfo;
import com.elitel.frame.web.cfgdbinfo.entity.DbTreeMap;
import com.elitel.frame.web.cfgdbinfo.service.CfgDbInfoService;
import com.elitel.frame.web.cfgtableinfo.dao.CfgTableInfoDao;
import com.elitel.frame.web.cfgtableinfo.entity.CfgTableInfo;
import com.elitel.frame.web.common.idworker.IdWorker;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;

/**
 * 数据库连接表(CfgDb)表服务实现类
 *
 * @author makejava
 * @since 2025-05-19 15:28:12
 */
@Slf4j
@Service
public class CfgDbInfoServiceImpl implements CfgDbInfoService {
    /**
     * 默认JDBC URL
     */
    private static final String OPT_TYPE_URL = "URL";
    public static final String DisplayPassword = "**********";
    public static final String MongoDB = "MongoDB";
    @Resource
    private CfgDbInfoDao cfgDbInfoDao;
    @Resource
    private CfgTableInfoDao cfgTableInfoDao;

    @Resource
    CfgTableService cfgTableService;

    /**
     * 通过ID查询单条数据
     *
     * @param dbKey 主键
     * @return 实例对象
     */
    @Override
    public CfgDbInfo queryById(String dbKey) {
        return this.cfgDbInfoDao.queryById(dbKey);
    }

    /**
     * @param cfgDbInfo 筛选条件
     * @return 查询结果
     */
    @Override
    public List<CfgDbInfo> list(CfgDbInfo cfgDbInfo) {
        return this.cfgDbInfoDao.queryList(cfgDbInfo);
    }


    /**
     * 新增数据
     *
     * @param cfgDbInfo 实例对象
     * @return 实例对象
     */
    @Override
    public CfgDbInfo insert(CfgDbInfo cfgDbInfo) {
        this.cfgDbInfoDao.insert(cfgDbInfo);
        return cfgDbInfo;
    }

    /**
     * 修改数据
     *
     * @param cfgDbInfo 实例对象
     * @return 实例对象
     */
    @Override
    public CfgDbInfo update(CfgDbInfo cfgDbInfo) {
        this.cfgDbInfoDao.update(cfgDbInfo);
        return this.queryById(cfgDbInfo.getDbKey());
    }

    /**
     * 通过主键删除数据
     *
     * @param dbKey 主键
     * @return 是否成功
     */
    @Override
    public boolean deleteById(String dbKey) {
        return this.cfgDbInfoDao.deleteById(dbKey) > 0;
    }

    @Override
    public List<CfgDbInfo> getJoinList(CfgDbInfo cfgDbInfo) {
        return this.cfgDbInfoDao.queryJoinList(cfgDbInfo);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public String save(CfgDbInfo cfgDbInfo) {
        checkSchema(cfgDbInfo);
        String optType = cfgDbInfo.getOptType();
        if (!OPT_TYPE_URL.equals(optType)) {
            // 如果不是JDBC URL,就拼接出来
            cfgDbInfo.setDbUrl(this.setConfigDb(cfgDbInfo));
        } else {
            // 解析出来url中的ip,port,数据库,模式
            parseUrl(cfgDbInfo);
        }

        String dbKey = cfgDbInfo.getDbKey();
        CfgDbInfo db = cfgDbInfoDao.queryById(dbKey);
        if (db == null) {
            cfgDbInfo.setDbKey(IdWorker.createStrId());
            cfgDbInfo.setCreatedTime(LocalDateTime.now());
            cfgDbInfo.setCreatedBy(StringUtils.isEmpty(SysUserUtils.getLoginAccount()) ? "admin" : SysUserUtils.getLoginAccount());
            cfgDbInfo.setLinkStatus(1L);
            cfgDbInfoDao.insert(cfgDbInfo);
        } else {
            if (DisplayPassword.equals(cfgDbInfo.getDbPwd())) {
                cfgDbInfo.setDbPwd(db.getDbPwd());
            }
            cfgDbInfoDao.update(cfgDbInfo);
        }

        // 先删除 后插入
        cfgDbInfoDao.deleteExt(dbKey);
        cfgDbInfoDao.insertExt(cfgDbInfo);
        cfgDbInfoDao.deleteExtDbRel(dbKey);
        cfgDbInfoDao.insertDbExtRel(cfgDbInfo);
        return cfgDbInfo.getDbKey();
    }

    @Override
    public CfgDbInfo selectByDbKey(String dbKey) {
        return cfgDbInfoDao.queryById(dbKey);
    }

    @Override
    public List<CfgDbInfo> queryList(Integer pageNum, Integer pageSize, String seachTxt) {
        if (pageNum == null) pageNum = 1;
        if (pageSize == null) pageSize = 10;
        PageHelper.startPage(pageNum, pageSize);
        return cfgDbInfoDao.queryList2(seachTxt);
    }

    public List<DbTreeMap> getAll(String dbType, String dbK) {
        if (StringUtils.isEmpty(dbType) && StringUtils.isEmpty(dbK)) {
            return cfgDbInfoDao.getAllDB();
        }
        if (!StringUtils.isEmpty(dbType) && StringUtils.isEmpty(dbK)) {
            return cfgDbInfoDao.getAllSchemaByDB(dbType);
        }
        if (!StringUtils.isEmpty(dbK) ) {
            return cfgDbInfoDao.getAllTb(dbK);
        }
        return null;
    }

    public HashMap<String, Object> getItems(String db, String dbk, Integer pageNum, Integer pageSize) {
        if (pageNum == null) pageNum = 1;
        if (pageSize == null) pageSize = 10;
        HashMap<String, Object> retn = new HashMap<>();
        if (db != null && dbk != null) {
            PageResponseVo cfgTablebyPage = cfgTableService.getCfgTablebyPage(dbk, null, pageNum, pageSize, 1);
            retn.put("list", cfgTablebyPage.getRows());
            retn.put("total", cfgTablebyPage.getTotal());
            return retn;
        }
        PageHelper.startPage(pageNum, pageSize);
        List<CfgDbInfo> list = cfgDbInfoDao.getIP(db);
        PageInfo<CfgDbInfo> pageInfo = new PageInfo<>(list);
        List<CfgDbInfo> list1 = pageInfo.getList();
        for (CfgDbInfo cfgDbInfo : list1) {
            String dbKey = cfgDbInfo.getDbKey();
            Integer dBnum = cfgDbInfoDao.getCount(dbKey);
            cfgDbInfo.setDbNum(dBnum);
        }
        retn.put("list", list1);
        retn.put("total", pageInfo.getTotal());
        return retn;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteDbConfigInfo(String dbKey) {
        List<CfgTableInfo> cfgTables = cfgTableInfoDao.selectListByDbKey(dbKey);
        if (cfgTables != null && !cfgTables.isEmpty()) {
            throw new RuntimeException("该数据库下存在元数据表,不能删除");
        } else {
            cfgDbInfoDao.deleteById(dbKey);
            cfgDbInfoDao.updateDelFlag(dbKey);
        }
    }

    @Override
    public boolean testConnection(CfgDbInfo cfgDb) {
        log.info("******开始测试连接******:{}", JSONObject.toJSONString(cfgDb));
        if (DisplayPassword.equals(cfgDb.getDbPwd())) {
            // find before password
            CfgDbInfo cfgDbBefore = cfgDbInfoDao.queryById(cfgDb.getDbKey());
            cfgDb.setDbPwd(cfgDbBefore.getDbPwd());
        }
        if (!cfgDb.getOptType().equals(OPT_TYPE_URL)) {
            cfgDb.setDbUrl(setConfigDb(cfgDb));
        } else {
            parseUrl(cfgDb);
        }
        cfgDb.setDbSign(UUID.randomUUID().toString());
        if (MongoDB.equals(cfgDb.getDbVersion())) {
            MongoDBUtil.getInstance().createConnection(cfgDb.getDbUrl());
            return true;
        } else {
            return getConnection(cfgDb);
        }
    }

    public boolean getConnection(CfgDbInfo cfgdb) {
        Connection conn = null;
        boolean bConnect;
        String url = "";
        try {
            Class.forName(cfgdb.getDbDrivername());
            url = cfgdb.getDbUrl();
            String user = cfgdb.getDbUser();
            String password = cfgdb.getDbPwd();
            // 建立连接
            conn = DriverManager.getConnection(url, user, password);
            bConnect = true;
        } catch (Exception e) {
            e.printStackTrace();
            bConnect = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return bConnect;
    }

    private static void parseUrl(CfgDbInfo cfgDbInfo) {
        String url = cfgDbInfo.getDbUrl();
        String upperUrl = url.toUpperCase();
        if (upperUrl.contains("JDBC:ORACLE")) {
            // 解析ORACLE的URL
            String[] split = url.split("@");
            String ipPortDb = split[1];
            String[] ipPortDbArr = ipPortDb.split(":");
            cfgDbInfo.setDbIp(ipPortDbArr[0]);
            String ext1 = cfgDbInfo.getExt1();
            if ("SID".equalsIgnoreCase(ext1)) {
                cfgDbInfo.setDbPort(ipPortDbArr[1]);
                cfgDbInfo.setDbName(ipPortDbArr[2]);
            } else {
                String portDb = ipPortDbArr[1];
                String[] portDbArr = portDb.split("/");
                cfgDbInfo.setDbPort(portDbArr[0]);
                cfgDbInfo.setDbName(portDbArr[1]);
            }
        } else if (upperUrl.contains("JDBC:MYSQL")) {
            // 解析MYSQL的URL
            String[] split = url.split("//");
            String ipPortDb = split[1];
            String[] ipPortDbArr = ipPortDb.split(":");
            cfgDbInfo.setDbIp(ipPortDbArr[0]);
            String portAndDb = ipPortDbArr[1];
            cfgDbInfo.setDbPort(portAndDb.substring(0, portAndDb.lastIndexOf("/")));
            cfgDbInfo.setDbName(portAndDb.substring(portAndDb.lastIndexOf("/") + 1));
        } else if (upperUrl.startsWith("JDBC:SQLSERVER://")) {
            // 解析出其中的ip
            String[] split = upperUrl.split(";");
            String ip = split[0].split("//")[1];
            cfgDbInfo.setDbIp(ip);
        } else if (upperUrl.startsWith("JDBC:KINGBASE8://")) {
            // 解析出其中的ip
            String[] split = upperUrl.split("//");
            String ip = split[1].split(":")[0];
            cfgDbInfo.setDbIp(ip);
        } else if (upperUrl.startsWith("JDBC:DM://")) {
            // 解析出其中的ip
            String[] split = upperUrl.split("//");
            String ip = split[1].split(":")[0];
            cfgDbInfo.setDbIp(ip);
        } else if (upperUrl.startsWith("JDBC:POSTGRESQL://")) {
            // 解析出其中的ip
            String[] split = upperUrl.split("//");
            String ip = split[1].split(":")[0];
            cfgDbInfo.setDbIp(ip);
        } else if (upperUrl.startsWith("JDBC:IMPALA://")) {
            // 解析出其中的ip
            String[] split = upperUrl.split("//");
            String ip = split[1].split(":")[0];
            cfgDbInfo.setDbIp(ip);
        } else if (upperUrl.startsWith("MONGODB://")) {
            // 解析出其中的ip
            if (upperUrl.contains("@")) {
                String[] split = upperUrl.split("@");
                String ip = split[1].split(":")[0];
                cfgDbInfo.setDbIp(ip);
            } else {
                String[] split = upperUrl.split("//");
                String ip = split[1].split(":")[0];
                cfgDbInfo.setDbIp(ip);
            }
        }
    }


    public String setConfigDb(CfgDbInfo cfgDb) {
        String result = null;
        switch (cfgDb.getDbVersion()) {
            case "ORACLE":
                String url = cfgDb.getDbIp() + ":" + cfgDb.getDbPort() + ("SID".equals(cfgDb.getExt1()) ? ":" : "/") + cfgDb.getDbName();
                result = String.format("jdbc:oracle:thin:@%s", url);
                break;
            case "MYSQL":
                result = String.format("jdbc:mysql://%s/%s?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull", cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            case "SQLSERVER":
                result = String.format("jdbc:sqlserver://%s;port=%s;databaseName=%s;trustServerCertificate=true", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbName());
                break;
            case "KingbaseV8":
                result = String.format("jdbc:kingbase8://%s/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            case "DM8":
                result = String.format("jdbc:dm://%s:%s?schema=%s", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbSchema());
                break;
            case "POSTGRESQL":
                result = String.format("jdbc:postgresql://%s:%s/%s?currentSchema=%s", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbName(), cfgDb.getDbSchema());
                break;
            case "MongoDB":
                if (StringUtils.isNotBlank(cfgDb.getDbUser()) && StringUtils.isNotBlank(cfgDb.getDbPwd())) {
                    result = String.format("mongodb://%s:%s@%s/%s", cfgDb.getDbUser(), cfgDb.getDbPwd(), cfgDb.getDbIp(), cfgDb.getDbName());
                } else {
                    result = String.format("mongodb://%s/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                }
                break;
            case "KUDU":
                result = String.format("jdbc:impala://%s:21050/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            default:
                break;
        }
        return result;
    }


    /**
     * 当为oracle或达梦数据库时，若模式为空，则将模式名指定为用户同名的默认模式名
     *
     * @param cfgDb
     */
    private void checkSchema(CfgDbInfo cfgDb) {
        if ("ORACLE".equals(cfgDb.getDbVersion()) || "DM8".equals(cfgDb.getDbVersion())) {
            if (StringUtils.isBlank(cfgDb.getDbSchema())) {
                cfgDb.setDbSchema(cfgDb.getDbUser());
            }
        }
    }

}
